#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Date;
use Config::IniFiles;
use Time::Local;
use XML::LibXML;

require "/var/www/cgi-bin/gnugraph/rcoos/timeZone.lib";


my $graph_config="/var/myscheduler/graph.xml";
my $xp_graph = XML::LibXML->new->parse_file($graph_config);

my $sensor=0; 
my $platform="";
my %obs;

my ($station,$sus,$standardname,$uom,$formula,$value,$mdate,@mtime,@mtype,@sensorID,$lastUpdate);

my $tmpDir="/var/www/html/data/";

my ($sqlsta,$sth);


my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');
my $dbh = DBI->connect ("dbi:Pg:dbname=$db_name","$db_user","$db_passwd");
if (!defined $dbh) {die "Cannot connect to database!\n";}

my $sql = qq{
select m_date
,multi_obs.platform_handle
,obs_type.standard_name
,uom_type.standard_name
,m_value
,qc_level
,sensor.fixed_z
,sensor.s_order
,sensor.row_id
,platform.short_name
,m_type_display_order.row_id
from multi_obs
left join sensor on sensor.row_id=multi_obs.sensor_id
left join m_type on m_type.row_id=multi_obs.m_type_id
left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id and obs_type.multiple=0
left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
left join platform on platform.row_id=sensor.platform_id
left join organization on organization.row_id=platform.organization_id
left join m_type_display_order on m_type_display_order.m_type_id=multi_obs.m_type_id
where 
  m_date>(now()-interval '24 hours') 
  order by 2,11,9,1 desc
};

   $sth = $dbh->prepare($sql);
   $sth->execute();

   my $flag=0;
   while (my @row = $sth->fetchrow_array) {

       if($row[1] ne $platform){

           if($platform ne ""){ printtofile(); } # end of platform ne ""

           %obs=();
           $platform=$row[1];
           $station=$row[9];
           @mtime=();
           @mtype=(); 
           @sensorID=();          

       } # end of ne $platfo#get time stamp
 

       if($row[8]!=$sensor){
            $sensor=$row[8];
            push(@sensorID,$sensor);

            ($uom,$formula)=uom_convert($row[2],$row[3]);
            my @array1=split(/_/,$row[2]);
            $standardname="";
            for(my $i=0;$i<@array1;$i++){
                 $array1[$i]=ucfirst($array1[$i]);
                 $standardname=$standardname.$array1[$i]." ";
            }
            if($row[6] && ($row[6]-0)<=0){ $standardname=$standardname." (Depth: $row[6] m)"; }
            if($standardname){ 
             $standardname=$standardname. " ($uom)";
              
              push(@mtype,$standardname);
            }      
        } #end of sensor change


       $value=value_convert($formula,$row[4]);  
       $value=sprintf("%.2f",$value);
  
       $sus="";   
       if($row[5]==2){$sus=" (suspect)";}
       if($row[5]==1){$sus=" (bad)";}
       $value=$value.$sus;

       $mdate=$row[0];
       
       if(!(grep $_ eq $mdate, @mtime)) {
          #if($station eq "OCP1"){    print $mdate."\n"; }
          push(@mtime,$mdate);
       }

       #if($station eq "ILM2"){
           #print "mdate $mdate obs_type $standardname value $value\n";
       #} 
       $obs{mdate}{$mdate}{obs_type}{$standardname}{value}=$value;

       $flag=1;
   } #end of next record

   if($flag==1){ printtofile();}

  undef $sth;
  $dbh->disconnect();


   sub printtofile {

             my $file=$tmpDir.$station."1.html";
             open(HTML,">$file");

             my $dststring=tellmeedt();

             print HTML "<table width=100% cellspacing=1 cellpadding=1>";
             print HTML "<tr bgcolor=#ffffdd><td>MM-DD-YY<br>HH:MM ($dststring)</td>";       
             sort(@mtime);

             @mtime=sort(@mtime);
             my $count=@mtime-1;
             my $a=0;
             for(my $j=$count;$j>=0;$j--){
                
                my $lastUpdate=timeConvert($mtime[$j],$dststring);
                $lastUpdate=substr($lastUpdate,5,5)."-".substr($lastUpdate,2,2)."<br>".substr($lastUpdate,11,5);
                if(substr($lastUpdate,12,2)>=12){
                  $lastUpdate=$lastUpdate." PM";
                }
                else{
                  $lastUpdate=$lastUpdate." AM";
                 }

                 print HTML "<td>$lastUpdate</td>";
                 if($a==9){last;}
                 $a++;
              }#end of for time
           
              print HTML "</tr>";

              for(my $i=0;$i<@mtype;$i++){
                 if($i%2==1){
                     print HTML "<tr class=bodytext bgcolor=#ffffdd><td><a href=typeQuery.php?station=$station&platform_handle=$platform&sensor_id=$sensorID[$i]>$mtype[$i]</a></td>";
                 }
                 else {
                     print HTML "<tr class=bodytext class=bodytext bgcolor=#eeeeee><td><a href=typeQuery.php?station=$station&platform_handle=$platform&sensor_id=$sensorID[$i]>$mtype[$i]</a></td>";        
                 }
                 $a=0;
                 for(my $j=$count;$j>=0;$j--){
                      print HTML "<td>&nbsp;$obs{mdate}{$mtime[$j]}{obs_type}{$mtype[$i]}{value}</td>";
                      if($a==9){last;}
                      $a++;     
                 }
   
                 print HTML "</tr>";
              } #end of @mtype


    
            print HTML "<tr bgcolor=#ffffdd><td>MM-DD-YY<br>HH:MM (UTC)</td>";
    
            my $a=0;
            for(my $j=$count;$j>=0;$j--){
            
               $lastUpdate=substr($mtime[$j],5,5)."-".substr($mtime[$j],2,2)."<br>".substr($mtime[$j],11,5);
         
               if(substr($lastUpdate,12,2)>=12){
                 $lastUpdate=$lastUpdate." PM";
               }
               else{
                    $lastUpdate=$lastUpdate." AM";
               }
               print HTML "<td>$lastUpdate </td>";
               if($a==9){last;}
               $a++;
            }
            if(@mtime==0){
              my $a=0;
              for(my $j=$count;$j>=0;$j--){
                 
                 print HTML "<td>&nbsp; - &nbsp;</td>";
                 
                 if($a==9){last;}
                 $a++;
              }
            }
            print HTML "</tr>";

              print HTML "</table>";
              # now print all the onservations
              close(HTML);

}

sub uom_convert {

 my ($obs_type,$uom) = @_;

 my $unit_conversion = '';

 ##CONFIG_START
 #conversions for the following which were labeled wrong on the way in to db
 if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
 if ($uom eq 'millibar') { $uom = 'mb'; }
 if ($uom eq 'percent_saturation') { $uom = 'percent'; }
 ##CONFIG_END

  my $unit_conversion = $xp_graph->findvalue('//observation_list/observation[@standard_name="'.$obs_type.'.'.$uom.'"]/standard_uom_en');
  if ($unit_conversion eq '') { return ($uom,$unit_conversion); }

  my $y_title="";
  my $conversion_formula="";
  $y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/y_title');
  if($y_title eq ""){$y_title=$uom;}
  $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/conversion_formula');

 return ($y_title,$conversion_formula);

}

sub value_convert {

 my ($conversion_string,$measurement) = @_;
    $conversion_string =~ s/var1/$measurement/g;
 my $conversion_val = eval $conversion_string;
 return $conversion_val; 
}


